﻿using System;
using System.Collections.Generic;
using System.Text;
using QuanLyNhaSach.QuanLyNhaSachDTO;
using System.Data;
namespace QuanLyNhaSach.QuanLyNhaSachDao
{
    class PhieuThuDao
    {
        public static DataTable GetPhieuThuAll()
        {
            string sql = "select * from PhieuThu";
            return Database.ExcuQuery(sql);
        }
        public static DataTable GetPhieuThubaoCao()
        {
            string sql = "SELECT PhieuThu.MaKhachHang,PhieuThu.NgayThu,PhieuThu.SoTienThu, KhachHang.TenKhachHang FROM  PhieuThu PhieuThu INNER JOIN KhachHang KhachHang ON PhieuThu.MaKhachHang=KhachHang.MaKhachHang";

          //  string sql = " SELECT `PhieuThu`.`MaKhachHang`, `PhieuThu`.`NgayThu`, `PhieuThu`.`SoTienThu`, `KhachHang`.`TenKhachHang`FROM   `PhieuThu` `PhieuThu` INNER JOIN `KhachHang` `KhachHang` ON `PhieuThu`.`MaKhachHang`=`KhachHang`.`MaKhachHang`";


            return Database.ExcuQuery(sql);
        }
        public static int getTienNoKhachHang(PhieuThuDTO pt)
        {
            string sql = "select * from KhachHang where MaKhachHang="+Convert.ToInt32(pt.MaKhachHang)+" and active=True";
            DataTable dt= Database.ExcuQuery(sql);
            int a= Convert.ToInt32(dt.Rows[0]["TienNo"].ToString());
            return a;
        }
        public static int getTienThuPT(PhieuThuDTO pt)
        {
            string sql = "select * from PhieuThu where MaPhieuThu=" + pt.MaPhieuThu + " and active=True";
            DataTable dt = Database.ExcuQuery(sql);
            int a = Convert.ToInt32(dt.Rows[0]["SoTienThu"].ToString());
            return a;
        }
        public static void InsertPT(PhieuThuDTO pt)
        {
            bool active = true;
            string sql = "insert into PhieuThu(NgayThu,SoTienThu,MaKhachHang,Active) values('" + pt.NgayThu + "'," + pt.SoTienThu + ",'" + pt.MaKhachHang + "'," + active + ")";
            Database.ExcuNonQuery(sql);
        }
        public static void updateTienNoKH(int tien, PhieuThuDTO pt)
        {
            string sql;

            sql = "Update KhachHang set TienNo= " + tien + " where MaKhachHang= " + pt.MaKhachHang + "";
            Database.ExcuNonQuery(sql);
           
        }
        public static void updateTienThuPT(int tien, PhieuThuDTO pt)
        {
            string sql;

            sql = "Update PhieuThu set SoTienThu= " + tien + " where MaPhieuThu= " + pt.MaPhieuThu + "";
            Database.ExcuNonQuery(sql);

        }
        public static void Delete(PhieuThuDTO pt)
        {
            
                string sql = "delete from PHIEUTHU where MaPhieuThu= " + pt.MaPhieuThu + "";
                Database.ExcuNonQuery(sql);
            
        }
           
    }
}
